import  pymysql
settings = { 'host':'172.16.10.201','user':'zy001','port':3306,'password':'zy@123','database':'crawl-bk','charset' : 'utf8'}
conn = pymysql.connect(**settings)
cursor = conn.cursor()


source_arrive = {'beike_community':'beike_community_total',
                'bieke_chengjiao_detail':'bieke_chengjiao_detail_total',
                'bieke_sale':'bieke_sale_total'}



# source_arrive = {'beike_community_total':'beike_community',
#                 'bieke_chengjiao_total':'bieke_chengjiao',
#                 'bieke_chengjiao_detail_total':'bieke_chengjiao_detail',
#                 'bieke_sale_total':'bieke_sale'}


for source, arrive in source_arrive.items():
    try:
        # 查询表字段
        sql_feild = "select COLUMN_NAME from information_schema.COLUMNS where table_name = "+ "'"+source+"'" +"and table_schema = 'crawl-bk';"
        cursor.execute(sql_feild)
        fields = cursor.fetchall()
        s = ''
        for i in fields[1:]:
            s = s + i[0]+','
        s = s[:-1]
        # 数据插入
        insert_sql = "INSERT INTO  "+  arrive +' ('+s+') ' +" SELECT "+ s + "  FROM " +  source
        cursor.execute(insert_sql)
        conn.commit()
        # 清空原表
        # TRUNCATE = 'TRUNCATE table '+source
        # cursor.execute(TRUNCATE)
        # conn.commit()
    except Exception as e:
        print(e)
        conn.rollback()



cursor.close()
conn.close()
print('成功追加！！！')
        
